<?php

include '../domain/KilometerEntry.php';
require_once('../domain/User.php');


/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of KilometerEntryDAO
 *
 * @author riemerb
 */
class KilometerEntryDAO {

    public static function getRoutes($userId) {
        //TODO: Umbau auf Class Database 
        //$db = Database::getDB();
        global $db;
        $query = 'SELECT r.id, DATE_FORMAT(r.eintrag_von,\'%d.%m.%Y %k:%i:%s\') AS eintrag_von, DATE_FORMAT(r.gefahren_bis,\'%d.%m.%Y\') AS gefahren_bis, DATE_FORMAT(r.gefahren_von,\'%d.%m.%Y\') AS gefahren_von, r.gefahrene_kilometer, r.pinnwand_eintrag
              FROM routen_eintrag r
              INNER JOIN user u on (u.id = r.user_fk)
              WHERE  u.id = :userId';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':userId', $userId);
            $statement->execute();
            $results = $statement->fetchAll();
            $statement->closeCursor();
            $routes = KilometerEntryDAO::genDBO($results);
            return $routes;
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }   
        return $routes;
    }
    
    /**
     * Liefert die letzten "$count" Routen.
     * @global type $db
     * @param type $count
     * @return type 
     */
    public static function getLastRoutes($count) {
        //TODO: Umbau auf Class Database 
        //$db = Database::getDB();
        global $db;
        $query = 'SELECT r.id, r.eintrag_von AS dat_eintrag, DATE_FORMAT(r.eintrag_von,\'%d.%m.%Y\') AS eintrag_von, DATE_FORMAT(r.gefahren_bis,\'%d.%m.%Y\') AS gefahren_bis, DATE_FORMAT(r.gefahren_von,\'%d.%m.%Y\') AS gefahren_von, r.gefahrene_kilometer, r.pinnwand_eintrag, u.nickname AS nickname
              FROM routen_eintrag r
              INNER JOIN user u on (u.id = r.user_fk)
              where length(r.pinnwand_eintrag) != 0
              ORDER BY dat_eintrag desc
              LIMIT 0 , :count';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':count', $count, PDO::PARAM_INT);
            $statement->execute();
            $results = $statement->fetchAll();
            $statement->closeCursor();
            $routes = KilometerEntryDAO::genDBO($results);
            return $routes;
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }   
        return $routes;
    }

        /**
     * Liefert die letzten "$count" Routen.
     * @global type $db
     * @param type $count
     * @return type 
     */
    public static function getRoutesPinnEntries($from, $count) {
        //TODO: Umbau auf Class Database 
        //$db = Database::getDB();
        global $db;
        $query = 'SELECT r.id, r.eintrag_von dat_eintrag, DATE_FORMAT(r.eintrag_von,\'%d.%m.%Y\') AS eintrag_von, r.pinnwand_eintrag AS pinnwand_eintrag, u.nickname AS nickname
              FROM routen_eintrag r
              INNER JOIN user u on (u.id = r.user_fk)
              WHERE length(r.pinnwand_eintrag) != 0
              ORDER BY dat_eintrag desc
              LIMIT :from , :count';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':from', $from, PDO::PARAM_INT);
            $statement->bindValue(':count', $count, PDO::PARAM_INT);
            $statement->execute();
            $results = $statement->fetchAll();
            $statement->closeCursor();
            //$routes = KilometerEntryDAO::genDBO($results);
            return $results;
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }   
        return $routes;
    }

    

    public static function getRoutesFromTo($userId, $from, $count) {
       global $db;
       $query = 'SELECT r.id, DATE_FORMAT(r.eintrag_von,\'%d.%m.%Y %k:%i:%s\') AS eintrag_von, DATE_FORMAT(r.gefahren_bis,\'%d.%m.%Y\') AS gefahren_bis, DATE_FORMAT(r.gefahren_von,\'%d.%m.%Y\') AS gefahren_von, r.gefahrene_kilometer, r.pinnwand_eintrag
                 FROM routen_eintrag r 
                 INNER JOIN user u on (u.id = r.user_fk) 
                 WHERE  u.id = :userId
                 ORDER BY r.gefahren_von desc
                 LIMIT :from , :count';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':userId', $userId);
            $statement->bindValue(':from', $from, PDO::PARAM_INT);
            $statement->bindValue(':count', $count, PDO::PARAM_INT);
            $statement->execute();
            $results = $statement->fetchAll();
            $statement->closeCursor();
            $routes = KilometerEntryDAO::genDBO($results);
            return $routes;
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }   
        return $routes;
    }

    
    public static function getSumAndCount($userId) {
       global $db;
       $query = 'SELECT sum(r.gefahrene_kilometer) AS sum, count(r.id) AS count 
                 FROM routen_eintrag r 
                 INNER JOIN user u on (u.id = r.user_fk) 
                 WHERE  u.id = :userId';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':userId', $userId);
            $statement->execute();
            $sumAndCount = $statement->fetchAll();
            $statement->closeCursor();
            return $sumAndCount;
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }   
        return $routes;
    }

    public static function getSumAndCountForSaison($userId, $saison) {
       global $db;
       
        $time_from = strtotime(trim($saison->getBeginn()));
        $time_to = strtotime(trim($saison->getEnde()));
        $string_from = date('Y-m-d', $time_from);
        $string_to = date('Y-m-d', $time_to);

       
       $query = 'SELECT sum(r.gefahrene_kilometer) AS sum 
                 FROM routen_eintrag r 
                 INNER JOIN user u on (u.id = r.user_fk) 
                 WHERE  u.id = :userId and r.gefahren_von >= :dateFrom and r.gefahren_bis <= :dateTo';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':userId', $userId);
            $statement->bindValue(':dateFrom', $string_from, PDO::PARAM_STR);
            $statement->bindValue(':dateTo', $string_to, PDO::PARAM_STR);

            $statement->execute();
            $result = $statement->fetchAll();
            $statement->closeCursor();
            $sumSaison = $result[0]['sum'];
            return $sumSaison;
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }   
        return $routes;
    }

    public static function getCountAll() {
       global $db;
       $query = 'SELECT count(r.id) AS count 
                 FROM routen_eintrag r 
                 INNER JOIN user u on (u.id = r.user_fk)
                 WHERE  length(r.pinnwand_eintrag) != 0';
        try {
            $statement = $db->prepare($query);
            $statement->execute();
            $result = $statement->fetchAll();
            $statement->closeCursor();
            $count = $result[0]['count'];
            return $count;
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }   
        return $routes;
    }

    /*
     * Alle gefahrenen Kilometer
     */
    public static function getTotalSum() {
       global $db;
       $query = 'SELECT sum(r.gefahrene_kilometer) AS sum FROM routen_eintrag r';
        try {
            $statement = $db->prepare($query);
            $statement->execute();
            $sum = $statement->fetchAll();
            $statement->closeCursor();
            return $sum[0]['sum'];
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }   
    }

    /*
     * Alle gefahrenen Kilometer
     */
    public static function getTotalSumFromSaison($saison) {
        global $db;
       
        $time_from = strtotime(trim($saison->getBeginn()));
        $time_to = strtotime(trim($saison->getEnde()));
        $string_from = date('Y-m-d', $time_from);
        $string_to = date('Y-m-d', $time_to);

        $query = 'SELECT sum(r.gefahrene_kilometer) AS sum FROM routen_eintrag r 
                  where r.gefahren_von >= :dateFrom and r.gefahren_bis <= :dateTo';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':dateFrom', $string_from, PDO::PARAM_STR);
            $statement->bindValue(':dateTo', $string_to, PDO::PARAM_STR);
            $statement->execute();
            $sum = $statement->fetchAll();
            $statement->closeCursor();
            return $sum[0]['sum'];
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }   
    }

    public static function countAllVotes() {
        global $db;
        $query = 'SELECT COUNT(DISTINCT(user_fk)) AS count FROM routen_eintrag';

        try {
            $statement = $db->prepare($query);
            $statement->execute();
            $result = $statement->fetchAll();
            $statement->closeCursor();
            return intval($result[0]['count']);
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }
    }

    public static function countAllVotesForSaison($saison) {
        global $db;
        
        $time_from = strtotime(trim($saison->getBeginn()));
        $time_to = strtotime(trim($saison->getEnde()));
        $string_from = date('Y-m-d', $time_from);
        $string_to = date('Y-m-d', $time_to);
        
        $query = 'SELECT COUNT(DISTINCT(user_fk)) AS count FROM routen_eintrag r '
               . 'where r.gefahren_von >= :dateFrom and r.gefahren_bis <= :dateTo';

        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':dateFrom', $string_from, PDO::PARAM_STR);
            $statement->bindValue(':dateTo', $string_to, PDO::PARAM_STR);
            $statement->execute();
            $result = $statement->fetchAll();
            $statement->closeCursor();
            return intval($result[0]['count']);
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }
    }


    public static function saveRoute($gefahren_von, $gefahren_bis, $gefahren_kilometer, $pinnwand_eintrag, $userId){
        global $db;
        $query = 'INSERT INTO routen_eintrag
                 (eintrag_von, gefahren_bis, gefahren_von, gefahrene_kilometer, pinnwand_eintrag, user_fk)
              VALUES
                 (now(), STR_TO_DATE(:gefahren_bis, \'%d.%m.%Y\'), STR_TO_DATE(:gefahren_von, \'%d.%m.%Y\'), :gefahren_kilometer, :pinnwand_eintrag, :userId)';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':gefahren_bis', $gefahren_bis);
            $statement->bindValue(':gefahren_von', $gefahren_von);
            $statement->bindValue(':gefahren_kilometer', $gefahren_kilometer,  PDO::PARAM_INT);
            $statement->bindValue(':pinnwand_eintrag', $pinnwand_eintrag);
            $statement->bindValue(':userId', $userId);
            $statement->execute();
            $statement->closeCursor();

            // Get the last product ID that was automatically generated
            $route_id = $db->lastInsertId();
            return $route_id;
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }
    }

    public static function genDBO($results) {
        $routes = array();
        foreach ($results as $row) {
            $route = new KilometerEntry(
                            $row['id'],
                            $row['eintrag_von'],
                            $row['gefahren_bis'],
                            $row['gefahren_von'],
                            $row['gefahrene_kilometer'],
                            $row['pinnwand_eintrag']);
            
            if (isset($row['nickname'])){
                $user = new User();
                $user->setNickname($row['nickname']);
                $route->setUser($user);
            }
            
            $routes[] = $route;
        }
        return $routes;
    }

    public static function checkOuterEntries($from, $to, $userId ) {
       global $db;
       $query ='select DATE_FORMAT(r.eintrag_von,\'%d.%m.%Y %k:%i:%s\') AS eintrag_von from routen_eintrag r
                where r.user_fk = :user_fk AND
                (r.gefahren_von >= STR_TO_DATE(:from,\'%d.%m.%Y\') && r.gefahren_von <= STR_TO_DATE(:to,\'%d.%m.%Y\')
                || r.gefahren_bis >= STR_TO_DATE(:from,\'%d.%m.%Y\') && r.gefahren_bis <= STR_TO_DATE(:to,\'%d.%m.%Y\')
                || r.gefahren_von <= STR_TO_DATE(:from,\'%d.%m.%Y\') && r.gefahren_bis >= STR_TO_DATE(:to,\'%d.%m.%Y\'))';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':from', $from);
            $statement->bindValue(':to', $to);
            $statement->bindValue(':user_fk', $userId, PDO::PARAM_INT);
            $statement->execute();
            $result = $statement->fetchAll();
            $statement->closeCursor();
            return $result;
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }   
    }

    public static function checkOuterEntriesForUpdate($from, $to, $userId, $routeUpdateId) {
       global $db;
       $query ='select DATE_FORMAT(r.eintrag_von,\'%d.%m.%Y %k:%i:%s\') AS eintrag_von from routen_eintrag r
                where r.user_fk = :user_fk AND r.id <> :routeUpdateId AND
                (r.gefahren_von >= STR_TO_DATE(:from,\'%d.%m.%Y\') && r.gefahren_von <= STR_TO_DATE(:to,\'%d.%m.%Y\')
                || r.gefahren_bis >= STR_TO_DATE(:from,\'%d.%m.%Y\') && r.gefahren_bis <= STR_TO_DATE(:to,\'%d.%m.%Y\')
                || r.gefahren_von <= STR_TO_DATE(:from,\'%d.%m.%Y\') && r.gefahren_bis >= STR_TO_DATE(:to,\'%d.%m.%Y\'))';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':from', $from);
            $statement->bindValue(':to', $to);
            $statement->bindValue(':user_fk', $userId, PDO::PARAM_INT);
            $statement->bindValue(':routeUpdateId', $routeUpdateId, PDO::PARAM_INT);
            $statement->execute();
            $result = $statement->fetchAll();
            $statement->closeCursor();
            return $result;
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }   
    }

    public static function updateRoute($gefahren_von, $gefahren_bis, $gefahren_kilometer, $pinnwand_eintrag, $userId, $id){
        global $db;
        $query = 'UPDATE routen_eintrag 
                 SET gefahren_bis = STR_TO_DATE(:gefahren_bis, \'%d.%m.%Y\'), 
                    gefahren_von = STR_TO_DATE(:gefahren_von, \'%d.%m.%Y\'), 
                    gefahrene_kilometer = :gefahren_kilometer, 
                    pinnwand_eintrag = :pinnwand_eintrag
                 WHERE user_fk = :userId AND id = :id';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':gefahren_bis', $gefahren_bis);
            $statement->bindValue(':gefahren_von', $gefahren_von);
            $statement->bindValue(':gefahren_kilometer', $gefahren_kilometer,  PDO::PARAM_INT);
            $statement->bindValue(':pinnwand_eintrag', $pinnwand_eintrag);
            $statement->bindValue(':userId', $userId);
            $statement->bindValue(':id', $id);            
            $statement->execute();
            $statement->closeCursor();

            // Get the last product ID that was automatically generated
            $route_id = $db->lastInsertId();
            return $route_id;
        } catch (PDOException $e) {
            $error_message = $e->getMessage();
            display_db_error($error_message);
        }
    }
}

?>
